package jp.co.mclnet.util;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jp.co.mclnet.dao.JdbcDaoImpl;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class HashidumeCalc {

    private static final String JDBC_PASSWORD = "";
    private static final String JDBC_URL = ""
            + "jdbc:mysql://localhost:3306/saturday?autoReconnect=true&amp;"
            + "useUnicode=true&amp;characterEncoding=utf-8";
    private static final String JDBC_USERNAME = "root";

    private static List<HashMap> getAllValues() throws Exception {
        JdbcDaoImpl dao = new JdbcDaoImpl("xyz_display");

        HashMap dto = new HashMap();

        List values = new ArrayList();
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME,
                    JDBC_PASSWORD);
            dao.setConnection(conn);
            Iterator<HashMap> read = dao.like(dto);

            while (read.hasNext()) {
                HashMap hashMap = (HashMap) read.next();
                values.add(hashMap);
            }

        } finally {
            if (conn != null) {
                conn.close();
            }
        }

        return values;
    }

    public static void main(String[] args) throws Exception {
        JdbcDaoImpl dao = new JdbcDaoImpl("xyz_display");

        HashMap condition = new HashMap();

        condition.put("value", "table=");

        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME,
                    JDBC_PASSWORD);
            dao.setConnection(conn);
            Iterator<HashMap> like = dao.like(condition);

            List<Map> target = new ArrayList();
            while (like.hasNext()) {
                HashMap hashMap = (HashMap) like.next();
                target.add(hashMap);
            }

            List<HashMap> allValues = getAllValues();

            List table = new ArrayList();
            for (int i = 0; i < target.size(); i++) {
                Map map = new HashMap();

                List z = new ArrayList();
                for (int j = 0; j < allValues.size(); j++) {
                    if (target.get(i).get("z")
                            .equals(allValues.get(j).get("z"))) {
                        z.add(allValues.get(j));
                    }
                }
            }

            System.out.println();

        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }

    public static void main2(String[] args) {
        String filePath = "deprecated/xls/display.xls";
        InputStream is = null;

        try {
            try {
                is = new FileInputStream(filePath);
            } catch (FileNotFoundException e) {
                throw new IllegalArgumentException(e);
            }
            is = new BufferedInputStream(is);
            POIFSFileSystem pfs = new POIFSFileSystem(is);

            HSSFWorkbook book = new HSSFWorkbook(pfs);
            output4DB(book, filePath);

            for (int z = 0; z < book.getNumberOfSheets(); z++) {
                HSSFSheet sheet = book.getSheetAt(z);
                int firstRowNum = sheet.getFirstRowNum();
                int lastRowNum = sheet.getLastRowNum();
                for (int y = firstRowNum; y <= lastRowNum; y++) {
                    HSSFRow row = (HSSFRow) sheet.getRow(y);
                    if (row == null) {
                        continue;
                    }
                    short firstCellNum = row.getFirstCellNum();
                    short lastCellNum = row.getLastCellNum();
                    for (short x = firstCellNum; x <= lastCellNum; x++) {
                        HSSFCell cell = (HSSFCell) row.getCell((int) x);
                        if (cell == null) {
                            continue;
                        }
                        Object value = CalcUtils.getValue(cell);
                        System.out.print(value);
                        System.out.print("[");
                        System.out.print(value.getClass().getName());
                        System.out.println("]");
                    }
                }
            }
        } catch (IOException e) {
            throw new IllegalArgumentException(e);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    throw new IllegalStateException(e);
                }
            }
        }
    }

    private static void output4DB(HSSFWorkbook book, String filepath) {
        System.out.println("-- for DB --");

        // 対象のテーブルを判定する

        String[] split = filepath.split("/");

        if ("speed.xls".equals(split[split.length - 1])) {
            // speed.xls:シート名からテーブルを判断
            aaaaaaaa(book);
        } else {
            // desplay.xls:"table=XXX"を探す
            aaaaaaaa(book);
        }

        // desplay.xls:"table=XXX"を探す
        // speed.xls:満たさなかったらシート名からテーブルを判断
        // テーブルに対応する情報をdtoに持たせる

        // 該当するdaoを生成する
        // insertする

        for (int z = 0; z < book.getNumberOfSheets(); z++) {
            HSSFSheet sheet = book.getSheetAt(z);
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            for (int y = firstRowNum; y <= lastRowNum; y++) {
                HSSFRow row = (HSSFRow) sheet.getRow(y);
                if (row == null) {
                    continue;
                }
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                for (short x = firstCellNum; x <= lastCellNum; x++) {
                    HSSFCell cell = (HSSFCell) row.getCell((int) x);
                    if (cell == null) {
                        continue;
                    }
                    Object value = CalcUtils.getValue(cell);
                    System.out.print(value);
                    System.out.print("[");
                    System.out.print(value.getClass().getName());
                    System.out.println("]");
                }
            }
        }
    }

    private static void aaaaaaaa(HSSFWorkbook book) {
        Map table = new HashMap();
        Set set = new HashSet();

        for (int z = 0; z < book.getNumberOfSheets(); z++) {
            HSSFSheet sheet = book.getSheetAt(z);

            List<Object> values = new ArrayList<Object>();

            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();

            for (int y = firstRowNum; y <= lastRowNum; y++) {
                HSSFRow row = (HSSFRow) sheet.getRow(y);
                if (row == null) {
                    continue;
                }
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                for (short x = firstCellNum; x <= lastCellNum; x++) {
                    HSSFCell cell = (HSSFCell) row.getCell((int) x);
                    if (cell == null) {
                        continue;
                    } else {
                        String strValue = CalcUtils.getValue(cell).toString();

                        if (strValue.startsWith("table=")) {
                            bbbbbb(lastCellNum, x, sheet, y, lastRowNum);

                            String tableName = strValue.substring(
                                    "table=".length(), strValue.length() - 1);
                            break;
                        }
                    }
                }
            }

            table.put(sheet.getSheetName(), null);
        }
    }

    private static void bbbbbb(short lastCellNum, short x, HSSFSheet sheet,
            int y, int lastRowNum) {
        int cols = 0;

        HSSFRow row = (HSSFRow) sheet.getRow(y + 1);

        Map rows = new HashMap();
        Map value = new HashMap();

        for (int i = 0; i + x <= lastCellNum; i++) {
            HSSFCell cell = (HSSFCell) row.getCell((int) i + x);
            if (cell == null) {
                cols = i;
                break;
            } else {
                value.put(i, CalcUtils.getValue(cell));
            }
        }

        rows.put(0, value);

        for (int indexY = 1; indexY + y < lastRowNum; indexY++) {

            row = (HSSFRow) sheet.getRow(y + indexY + 1);

            value = new HashMap();
            for (int i = 0; i < cols; i++) {
                HSSFCell cell = (HSSFCell) row.getCell((int) i + x);

                if (cell == null) {
                    if (i == 0) {
                        break;
                    } else {
                        value.put(i, null);
                    }

                    continue;
                } else {
                    value.put(i, CalcUtils.getValue(cell));
                }
            }
        }
    }
}
